Select statement with field from used under different names depening on the values of other field in the same table

Question

Hi, Could anybody tell me please how to write the select statement where one field can be used under different names depending on the value of other field in the same table? Thanks

Aarti · Answer

Hi Nel.. Please Describe your question in detail. as i am getting .. you can use ALIAS.... Thanks.

Nel · Answer

Could you tell me please is there another way how to make crystal report with columns which have different names, but take values from same columns depending on values of another column of the table?

Nel · Answer

I tried that.
I tried like:
1.case when MAGACIN.SIFRA='0991000' then [MAGACIN.KOL] else null end as 'Gorivo', case when MAGACIN.SIFRA='0993050' then [MAGACIN.KOL] else null end as 'Maslo'

2. case when [MAGACIN.SIFRA]='0991000' then [MAGACIN.KOL] else null end as 'Gorivo', case when [MAGACIN.SIFRA]='0993050' then [MAGACIN.KOL] else null end as 'Maslo'

3.case when [MAGACIN.SIFRA]='0991000' then MAGACIN.KOL else null end as 'Gorivo', case when [MAGACIN.SIFRA]='0993050' then MAGACIN.KOL else null end as 'Maslo'
and I still get the same error in all the cases, in all my tries of this like the first one, then like the 2, and when tried like 3.

Pravin More · Answer

nel, You might have a FieldName or tablename the same as one of the reserved keywords. Try putting brackets around the [ FieldName ] . try n let me know...

Nel · Answer

I still have the same problem, I get the same error on row oleDBDataAdapter1.SelectCommand=command;

Pravin More · Answer

you have written 2 cases in one open-close bracket..... complite it properly or remove bracket and then try it once might be
solve......

try like this......

"SELECT NOVI.GBR as gbrnov, NOVI.AB as abnov, NALOG1.DATA as datanov,Min(NALOG1.POCKM) AS MinOfPOCKM, Max(NALOG1.KRAJKM) AS MaxOfKRAJKM,(Max(NALOG1.KRAJKM)-Min(NALOG1.POCKM)) AS RAZLIKA, (MAGACIN.KOL)/100 AS Potr100km,case when MAGACIN.SIFRA='0991000' then MAGACIN.KOL else null end as 'Gorivo', case when MAGACIN.SIFRA='0993050' then MAGACIN.KOL else null end as 'Maslo', NOVI.DATAP AS poslprov, NOVI.DATAS AS poslservis FROM (NALOG1 INNER JOIN MAGACIN ON NALOG1.GBRV=MAGACIN.GBR) INNER JOIN NOVI ON NALOG1.GBRV=NOVI.GBR where ( ( ( (NOVI.GBR)>=? And (NOVI.GBR)<=? ) ) AND ( (NOVI.AB)=? or (NOVI.AB)=?) AND ( ( (NALOG1.DATA)>=?) and ((NALOG1.DATA)<=?) ) ) GROUP BY NOVI.GBR, NOVI.AB, NALOG1.DATA, NOVI.DATAP, NOVI.DATAS"

Nel · Answer

MAGACIN.SIFRA is text type and I am working in C#

Pravin More · Answer

hmm.... i have no idea about this error but i think it might be related to datatype mismatch

what is datatype of MAGACIN.SIFRA?
if its not string then try like MAGACIN.SIFRA=0991000

in which enviroment you are writing this query i mean un sql editor or c# code?

Thanks,
Pravin.

Nel · Answer

I tried your example Pravin and now I get this error:

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

and my code looks like this:

SELECT NOVI.GBR as gbrnov, NOVI.AB as abnov, NALOG1.DATA as datanov,Min(NALOG1.POCKM) AS MinOfPOCKM, Max(NALOG1.KRAJKM) AS MaxOfKRAJKM,(Max(NALOG1.KRAJKM)-Min(NALOG1.POCKM)) AS RAZLIKA, (MAGACIN.KOL)/100 AS Potr100km,( case when MAGACIN.SIFRA='0991000' then MAGACIN.KOL else null end as 'Gorivo', case when MAGACIN.SIFRA='0993050' then MAGACIN.KOL else null end as 'Maslo'), NOVI.DATAP AS poslprov, NOVI.DATAS AS poslservis FROM (NALOG1 INNER JOIN MAGACIN ON NALOG1.GBRV=MAGACIN.GBR) INNER JOIN NOVI ON NALOG1.GBRV=NOVI.GBR where ( ( ( (NOVI.GBR)>=? And (NOVI.GBR)<=? ) ) AND ( (NOVI.AB)=? or (NOVI.AB)=?) AND ( ( (NALOG1.DATA)>=?) and ((NALOG1.DATA)<=?) ) ) GROUP BY NOVI.GBR, NOVI.AB, NALOG1.DATA, NOVI.DATAP, NOVI.DATAS"

Pravin More · Answer

yes Nel i got your query...........

use case when...then like below query........

select case when a.name='pravin' then a.Address else null end as 'Pravins Address',
case when a.name='Nel' then a.Address else null end as 'Nels Address'

it will work in you case...........

Note:- change sytax according to your enviroment i have written for sql server.

Thanks
Pravin.

Nel · Answer

I have this code for the select command in my crystal report:

SELECT NOVI.GBR as gbrnov, NOVI.AB as abnov, NALOG1.DATA as datanov,Min(NALOG1.POCKM) AS MinOfPOCKM, Max(NALOG1.KRAJKM) AS MaxOfKRAJKM,(Max(NALOG1.KRAJKM)-Min(NALOG1.POCKM)) AS RAZLIKA, MAGACIN.KOL AS Gorivo, (MAGACIN.KOL)/100 AS Potr100km, MAGACIN.KOL AS Maslo, NOVI.DATAP AS poslprov, NOVI.DATAS AS poslservis FROM (NALOG1 INNER JOIN MAGACIN ON NALOG1.GBRV=MAGACIN.GBR) INNER JOIN NOVI ON NALOG1.GBRV=NOVI.GBR where ( ( ( (NOVI.GBR)>=? And (NOVI.GBR)<=? ) ) AND ( (NOVI.AB)=? or (NOVI.AB)=?) AND ( ( (NALOG1.DATA)>=?) and ((NALOG1.DATA)<=?) )AND ((MAGACIN.SIFRA)='0991000') AND ((MAGACIN.SIFRA)='0993050') ) GROUP BY NOVI.GBR, NOVI.AB, NALOG1.DATA, MAGACIN.KOL, NOVI.DATAP, NOVI.DATAS

So I suppose I have problem because I use the same column under different aliases depending on the value of SIFRA column in the same table. The dataset remains empty and so is the report.
Before I put the bold text it worked fine.

I hope now it is clearer.
Thanks in advance

Pravin More · Answer

Hi Nel,
          you can try this....

             select case when a.name='pravin' then a.Address else null end as 'Pravins Address',
                      case when a.name='Nel'     then a.Address else null end as 'Nels Address'

              from UserTable

Thanks,
Pravin.

Karthik Agarwal · Answer

Try this:

private void GetRowsByFilter()
{
    DataTable table = DataSet1.Tables["Orders"];
    // Presuming the DataTable has a column named Date.
    string expression;
    expression = "Date > #1/1/00#";
    DataRow[] foundRows;

    // Use the Select method to find all rows matching the filter.
    foundRows = table.Select(expression);

    // Print column 0 of each returned row.
    for(int i = 0; i < foundRows.Length; i ++)
    {
        Console.WriteLine(foundRows[i][0]);
    }
}

Check these links for more explanation:

http://msdn.microsoft.com/en-us/library/det4aw50%28v=VS.90%29.aspx

http://idealprogrammer.com/net-languages/code-samples/select-statement-source-code/