Skip to main content
Question

Select Statement Assistance

  • July 27, 2026
  • 2 replies
  • 26 views
V
Do Gooder (Customer)

We want to have a list of states and our license numbers to work in those states. There is a built in table for states that I thought to use for this purpose. Before even adding license numbers to that table, I tried to supply a state code and get the state name from the table, however something is not working. I have used nested SELECT statements before elsewhere, but it seems to fail here.

The inside Select statement returns what I would expect. A state code from the address ID of the Customer. If I use a different state code that isn’t a select, but just a dummy variable, the outer Select statement returns the state name. But when I put it all together, it seems to fail.

Is anyone able to tell me what I am missing?

Using these arguments: v.Customer_No, v.Ship_Addr_No

SELECT State_Name FROM State_Codes_Tab WHERE State_Code = (SELECT State FROM Customer_Info_Address WHERE (Customer_Id = :Customer_No) AND (Address_Id = :Ship_Addr_No)) AND Country_Code = 'US'

 

    2 replies

    NickPorter
    Superhero (Customer)
    Forum|alt.badge.img+18
    • NickPorter
    • Superhero (Customer)
    • July 27, 2026

    It would be helpful if you could explain what you mean by ‘it seems to fail’.  No results? Error message?  Unexpected result?

    First thought is that you might have a difference in values since the state code may not be syntactically the same.  This might be fixable by applying ‘UPPER’ to the State value in the inner select and , for example.   

    Examples of what you’re using to test and the result or error you are getting would help us if the above doesn’t help you.

    HTH,

    Nick

    VivekBhandiwad
    Hero (Partner)
    Forum|alt.badge.img+8

    Dear ​@V2Platform ( John )

    One aspect that comes out of your query is that you are matching State_code (from TAB) with State (from view, in the subquery) .  This might be causing the discrepancy in your query.  it is highly possible that they are not the same ( as is with most IFS _DB values )  . As indicated by ​@NickPorter  the “upper” definition might help, or simply changing from STATES_CODE_TAB to STATES_CODE view  in the primary query, and then choosing the “State” column.  Viceversa, also shift to the TAB in the subquery and choose the right field to match.  Two different values from the outer query and inner query are being matched, hence the issue , I believe. 

    Regards

    Vivek

    VIBHIN
    Terms & ConditionsAccessibility statement